from pymysql import cursors, connect
from src.common.tool import root_path, env_active
import json


class DbConfig:

    def __init__(self, host: str, port: int, user: str, passwd: str, db='', db_type='ms', charset='utf8'):
        self.host = host
        self.db = db
        self.user = user
        self.port = port
        self.password = passwd
        self.db_type = db_type
        self.charset = charset

    def execute_query_one(self, sql):
        with DataBase(self) as db:
            db.execute(sql)
            return db.fetchone()

    def execute_query(self, sql):
        with DataBase(self) as db:
            db.execute(sql)
            result = db.fetchall()
            # pg默认返回元组，需要处理
            if self.db_type == 'pg':
                # 获取列信息
                columns = [desc[0] for desc in db.description]
                # 转换为字典
                return [dict(zip(columns, row)) for row in result]

            return result

    def execute_inserts(self, sqls):
        [self.execute_insert(sql) for sql in sqls]

    def execute_insert(self, sql):
        with DataBase(self, query=False) as db:
            # print("ds.py: execute insert: ", sql[:100], '...')
            db.execute(sql)

    def execute_update(self, sql):
        with DataBase(self, query=False) as db:
            # print("ds.py: execute update: ", sql[:100], '...')
            db.execute(sql)

    def execute_delete(self, sql):
        with DataBase(self, query=False) as db:
            db.execute(sql)

    def execute_insert_with_id(self, sql):
        with DataBase(self, query=False) as db:
            # print("ds.py: execute insert: ", sql[:100], '...')
            db.execute(sql)
            return db.lastrowid


class DataBase:
    def __init__(self, db_config: DbConfig, query=True):
        # 建立连接
        self.conn = connect(host=db_config.host, port=db_config.port, db=db_config.db, user=db_config.user,
                            passwd=db_config.password, charset=db_config.charset)
        # 创建游标，操作设置为字典类型
        self.cur = self.conn.cursor(cursor=cursors.DictCursor)
        self.query = query

    def __enter__(self):
        # 返回游标
        return self.cur

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 非查询语句提交数据库并执行
        if not self.query:
            self.conn.commit()
        # 关闭游标
        self.cur.close()
        # 关闭数据库连接
        self.conn.close()


with open(f'{root_path}/envs/config_{env_active}.json', 'r') as f:
    env_config = json.load(f)
# _DB 开头的是分环境的
AIDES_DB = DbConfig(host=env_config['MYSQL_HOST'],
                    port=env_config['MYSQL_PORT'],
                    user=env_config['MYSQL_USER'],
                    passwd=env_config['MYSQL_PASSWORD'],
                    db=env_config['MYSQL_DB'],
                    charset='utf8')
